package com.deng.etl.excel.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.deng.common.SQLHelper;
import com.deng.etl.excel.IExcelRule;
import com.deng.util.DateTools;

public class HuShiPaiBan extends SQLHelper implements IExcelRule {

	private Map<String, Integer> empMap = new HashMap<String, Integer>();
	private Map<String, Integer> voteMap = new HashMap<String, Integer>();
	private Map<String, Integer> classMap = new HashMap<String, Integer>();
	private Map<Integer, BigDecimal> classPointMap = new HashMap<Integer, BigDecimal>();
	public void importData(Workbook wb, String type_name,String id) {
		try{
			Connection conn = getConnect();
				Sheet sh=wb.getSheet(0);
				int rownum = sh.getRows();
				
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);//y
					if(c1 == null){
						continue;
					}
					if(c1.getContents() == null || "".equals(c1.getContents())){
						continue;
					}
					Cell c2 = sh.getCell(1,i);//m
					Cell c3 = sh.getCell(2,i);//xingming
					Cell c4 = sh.getCell(3,i);//gonghao
					Cell c5 = sh.getCell(4,i);//banbie
					Cell c6 = sh.getCell(5,i);//shuliang
					
					String stat_time = getTime(c1.getContents(),c2.getContents());
					Integer employee_id = getEmployeeIdByWorkId(conn, c4.getContents());
					String class_name = c5.getContents();
					Integer class_id = getClassIdByName(conn, class_name);
					BigDecimal count = new BigDecimal(0);
					try{
						count = new BigDecimal(c6.getContents().trim());
					}catch(Exception ex){
						ex.printStackTrace();
					}
					BigDecimal point = new BigDecimal(0);
					try{
						point = classPointMap.get(class_id);
					}catch(Exception ex){
						ex.printStackTrace();
					}
					
					
					String sql = "select count,valid from t_stat_employee_vs_class_month " +
							"	where class_id = "+class_id+
							" 	and employee_id = "+employee_id+
							" 	and stat_time = '"+stat_time+"'"+
							"	and dispense_organization_id = "+id;
					System.out.println(sql);
					PreparedStatement pst=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
					ResultSet rs=pst.executeQuery();
					if(rs.next()){
						int valid = rs.getInt("valid");
						if(valid == 1){//已经审核
							continue ;
						}else{//未审核
							rs.updateBigDecimal(1, count);
							rs.updateRow();
						}
					}else{
						sql = "insert into t_stat_employee_vs_class_month " +
						"	(class_id,employee_id," +
						"	count,stat_time,dispense_organization_id," +
						"	point,valid) " +
						"	values("+class_id+","+employee_id+","+
						count+",'"+stat_time+"',"+id+"," +
						point+",0)";
						updateData(conn, sql);
					}					
				}
				freeConnection(conn);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	private Integer getEmployeeIdByWorkId(Connection conn,String work_id){
		try{
			if(work_id == null || "".equals(work_id)){
				return 0;
			}
			if(empMap == null || empMap.size() == 0){
				String sql = "select employee_id,work_id from t_sys_stat_employee";
				ResultSet rs = queryData(conn, sql);
				while(rs.next()){
					empMap.put(rs.getString("work_id"), rs.getInt("employee_id"));
				}
			}
			return empMap.get(work_id);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
		
	}
	private Integer getClassIdByName(Connection conn,String name){
		try{
			if(name == null || "".equals(name)){
				return 0;
			}
			if(classMap == null || classMap.size() == 0){
				String sql = "select id,name,point from DICT_CLASS_TYPE";
				ResultSet rs = queryData(conn, sql);
				while(rs.next()){
					classMap.put(rs.getString("name"), rs.getInt("id"));
					classPointMap.put(rs.getInt("id"), rs.getBigDecimal("point"));
				}
			}
			return classMap.get(name);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
		
	}
	private String getTime(String year,String month){
		try{
			if(year == null || "".equals(year)){
				return "1900-1-1";
			}
			return year+"-"+month+"-1";
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return "1900-1-1";
		
	}

}
